"""
Case Type   : ustore_autovacuum
Case Name   : autovacuum_mode相关测试--清理模式为analyze
Create At   : 2022/01/17
Owner       : zhao-pengcheng-g
Description :
    1.设置清理模式为analyze
    2.执行DML语句
    3.查询last_autovacuum_time,last_autoanalyze_time
    4.清理资源，恢复参数
Expect      :
    1.设置成功
    2.执行DML语句成功
    3.查询成功，且数据正常
    4.清理资源成功，恢复参数成功
History     :
"""
import unittest
from yat.test import Node
from yat.test import macro
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger
from testcase.utils.Common import Common


class UstoreAutovacuumMode(unittest.TestCase):
    def setUp(self):
        self.logger = Logger()
        text = '---Opengauss_Function_Ustore_Autovacuum_Case0016 start---'
        self.logger.info(text)
        self.userNode = Node('PrimaryDbUser')
        self.sh_primy = CommonSH('PrimaryDbUser')
        self.constant = Constant()
        self.common = Common()
        self.item_mode = 'autovacuum_mode'
        self.item_naptime = 'autovacuum_naptime'
        self.item_autovacuum = 'autovacuum'
        self.show_mode_cmd = f'show {self.item_mode};'
        self.show_naptime_cmd = f'show {self.item_naptime};'
        self.show_autovacuum_cmd = f'show {self.item_autovacuum};'
        self.oid = 0
        self.table_name = 't_autovacuum_0016'
        self.procedure_name = 'p_init_autovacuum_0016'

        # text = '---检查参数，修改配置:undo_zone_count,expect:成功---'
        # self.logger.info(text)
        # self.config_undo = 'undo_zone_count'
        # self.show_undo_cmd = f'show {self.config_undo};'
        # check_undo = self.sh_primy.execut_db_sql(self.show_undo_cmd)
        # self.logger.info(check_undo)
        # self.undo_default = check_undo.splitlines()[-2].strip()
        # show_max_conn_cmd = f'show max_connections;'
        # show_max_conn_msg = self.sh_primy.execut_db_sql(show_max_conn_cmd)
        # self.logger.info(show_max_conn_msg)
        # max_conn = show_max_conn_msg.splitlines()[-2].strip()
        # self.undo_modify = 4 * int(max_conn)
        # if self.undo_modify != self.undo_default:
        #     configitem = f"{self.config_undo}={self.undo_modify}"
        #     result = self.common.config_set_modify(configitem)
        #     self.assertTrue(result, '执行失败' + text)
        # show_undo_msg = self.sh_primy.execut_db_sql(self.show_undo_cmd)
        # self.logger.info(show_undo_msg)
        # self.common.equal_sql_mdg(show_undo_msg, self.config_undo,
        #                           self.undo_modify, '(1 row)', flag='1')

        text = '---查询参数默认值，修改参数：autovacuum_mode，expect:成功---'
        self.logger.info(text)
        show_autovacuum_res = \
            self.sh_primy.execut_db_sql(self.show_autovacuum_cmd)
        self.logger.info(show_autovacuum_res)
        self.autovacuum_default = show_autovacuum_res.splitlines()[-2].strip()
        if 'on' != self.autovacuum_default:
            result = self.sh_primy.execute_gsguc('reload',
                                            self.constant.GSGUC_SUCCESS_MSG,
                                            f'{self.item_autovacuum}=on')
            self.assertTrue(result, '参数修改失败')
        check_msg = self.sh_primy.execut_db_sql(self.show_autovacuum_cmd)
        self.logger.info(check_msg)
        self.common.equal_sql_mdg(check_msg, f'{self.item_autovacuum}',
                                  'on', '(1 row)', flag='1')
        text = '---创建存储过程,expect:成功---'
        self.logger.info(text)
        sql_cmd_1 = f'''create or replace procedure {self.procedure_name} 
            (tname varchar ,a int,b int) as sqlst varchar(500);
            j int;
            begin 
            for i in a..b loop 
            j := i+521;
            sqlst := 'insert into '|| tname ||' 
            values ('||i||','||j||',''abcddeg'')'; 
            execute immediate sqlst;
            end loop;
            end;'''
        self.logger.info(sql_cmd_1)
        sql_msg_1 = self.sh_primy.execut_db_sql(sql_cmd_1)
        self.logger.info(sql_msg_1)
        self.assertIn(self.constant.CREATE_PROCEDURE_SUCCESS_MSG,
                      sql_msg_1, '执行失败' + text)
        text = '---创建测试表,expect:成功---'
        self.logger.info(text)
        sql_cmd_2 = f'''drop table if exists {self.table_name};
                    create table {self.table_name} 
                    (a int, b number, c varchar2(15)) 
                    WITH (STORAGE_TYPE=USTORE);
                    call {self.procedure_name}('{self.table_name}', 0, 10000);'''
        self.logger.info(sql_cmd_2)
        sql_msg_2 = self.sh_primy.execut_db_sql(sql_cmd_2)
        self.logger.info(sql_msg_2)
        self.assertIn(self.constant.CREATE_TABLE_SUCCESS and 'init_autovacuum',
                      sql_msg_2, '执行失败' + text)

    def test_autovacuum_mode(self):
        text = '---step1:设置清理模式为analyze，expect:设置成功---'
        self.logger.info(text)
        show_mode_res = self.sh_primy.execut_db_sql(self.show_mode_cmd)
        self.logger.info(show_mode_res)
        self.default_mode = show_mode_res.splitlines()[-2].strip()
        if 'analyze' != self.default_mode:
            guc_mode = self.sh_primy.execute_gsguc('reload',
                                            self.constant.GSGUC_SUCCESS_MSG,
                                            f'{self.item_mode} = analyze',
                                            single=True)
            self.assertTrue(guc_mode, '执行失败:' + text)
        show_mode_msg = self.sh_primy.execut_db_sql(self.show_mode_cmd)
        self.logger.info(show_mode_msg)
        self.assertIn('analyze', show_mode_msg, '执行失败:' + text)
        show_naptime_res = self.sh_primy.execut_db_sql(self.show_naptime_cmd)
        self.logger.info(show_naptime_res)
        self.default_naptime = show_naptime_res.splitlines()[-2].strip()
        if '30s' != self.default_naptime:
            guc_naptime = self.sh_primy.execute_gsguc('reload',
                                               self.constant.GSGUC_SUCCESS_MSG,
                                               'autovacuum_naptime = 30s',
                                               single=True)
            self.assertTrue(guc_naptime, '执行失败:' + text)
        show_naptime_msg = self.sh_primy.execut_db_sql(self.show_naptime_cmd)
        self.logger.info(show_naptime_msg)
        self.assertIn('30s', show_naptime_msg, '执行失败:' + text)

        text = '---step2:执行DML语句，expect:成功---'
        self.logger.info(text)
        sql_cmd = f'''select oid from pg_class 
                where relname = '{self.table_name}';
                update {self.table_name} set b = 321 where a <= 2100;
                delete from {self.table_name} where a<50;
                select pg_sleep(60);'''
        sql_exec_msg = self.sh_primy.execut_db_sql(sql_cmd)
        self.logger.info(sql_exec_msg)
        self.oid = sql_exec_msg.splitlines()[2].strip()
        self.assertNotEqual(self.oid, '0', '执行失败:' + text)
        self.assertIn(self.constant.UPDATE_SUCCESS_MSG and
                      self.constant.DELETE_SUCCESS_MSG and
                      'pg_sleep', sql_exec_msg, '执行失败:' + text)

        text = '---step3:查询last_autovacuum_time,last_autoanalyze_time,' \
               'expect:查询成功，且数据正常---'
        self.logger.info(text)
        sql_cmd = f'''select pg_stat_get_last_autovacuum_time({self.oid});
                      select pg_stat_get_last_autoanalyze_time({self.oid});'''
        sql_exec_msg = self.sh_primy.execut_db_sql(sql_cmd)
        self.logger.info(sql_exec_msg)
        vacuum_time = sql_exec_msg.splitlines()[2].strip()
        analyze_time = sql_exec_msg.splitlines()[-2].strip()
        date_cmd = f"select current_date;"
        date_msg = self.sh_primy.execut_db_sql(date_cmd)
        self.logger.info(date_msg)
        current_date = date_msg.splitlines()[-2].strip()
        self.assertIn(current_date[:10], analyze_time, '执行失败:' + text)
        self.assertNotIn(current_date[:10], vacuum_time, '执行失败:' + text)

    def tearDown(self):
        text = '---step4:资源清理，恢复参数，expect:成功---'
        self.logger.info(text)
        sql_cmd = f'drop procedure {self.procedure_name};' \
                  f'drop table {self.table_name};'
        self.logger.info(sql_cmd)
        sql_msg = self.sh_primy.execut_db_sql(sql_cmd)
        self.logger.info(sql_msg)

        # text = '---step:恢复参数:undo_zone_count,expect:成功---'
        # self.logger.info(text)
        # undo_res = self.sh_primy.execut_db_sql(self.show_undo_cmd)
        # self.logger.info(undo_res)
        # undo_current = undo_res.splitlines()[-2].strip()
        # if undo_current != self.undo_default:
        #     result = self.sh_primy.execute_gsguc('reload',
        #                                 self.constant.GSGUC_SUCCESS_MSG,
        #                             f'{self.config_undo}={self.undo_default}')
        #     self.assertTrue(result, '执行失败：' + text)
        # undo_msg = self.sh_primy.execut_db_sql(self.show_undo_cmd)
        # self.logger.info(undo_msg)

        text = '---恢复参数:autovacuum,expect:成功---'
        self.logger.info(text)
        autovacuum_res = self.sh_primy.execut_db_sql(self.show_autovacuum_cmd)
        self.logger.info(autovacuum_res)
        check_current = autovacuum_res.splitlines()[-2].strip()
        if check_current != self.autovacuum_default:
            result = self.sh_primy.execute_gsguc('reload',
                        self.constant.GSGUC_SUCCESS_MSG,
                        f'{self.item_autovacuum}={self.autovacuum_default}')
            self.assertTrue(result, '执行失败：' + text)
        autovacuum_msg = self.sh_primy.execut_db_sql(self.show_autovacuum_cmd)
        self.logger.info(autovacuum_msg)

        text = '---恢复参数:autovacuum_mode,expect:成功---'
        self.logger.info(text)
        check_mode_res = self.sh_primy.execut_db_sql(self.show_mode_cmd)
        self.logger.info(check_mode_res)
        current_mode = check_mode_res.splitlines()[-2].strip()
        if self.default_mode != current_mode:
            guc_result = self.sh_primy.execute_gsguc('reload',
                            self.constant.GSGUC_SUCCESS_MSG,
                            f'{self.item_mode} = {self.default_mode}',
                            single=True)
            self.assertTrue(guc_result, '执行失败:' + text)
        show_mode_msg = self.sh_primy.execut_db_sql(self.show_mode_cmd)
        self.logger.info(show_mode_msg)

        text = '---恢复参数:autovacuum_naptime,expect:成功---'
        self.logger.info(text)
        check_naptime_res = self.sh_primy.execut_db_sql(self.show_naptime_cmd)
        self.logger.info(check_naptime_res)
        current_naptime = check_naptime_res.splitlines()[-2].strip()
        if self.default_naptime != current_naptime:
            guc_result = self.sh_primy.execute_gsguc('reload',
                            self.constant.GSGUC_SUCCESS_MSG,
                            f'{self.item_naptime} = {self.default_naptime}',
                            single=True)
            self.assertTrue(guc_result, '执行失败:' + text)
        show_naptime_msg = self.sh_primy.execut_db_sql(self.show_naptime_cmd)
        self.logger.info(show_naptime_msg)

        self.assertIn(self.constant.DROP_PROCEDURE_SUCCESS_MSG and
                      self.constant.DROP_TABLE_SUCCESS,
                      sql_msg, '执行失败:' + text)
        # self.common.equal_sql_mdg(autovacuum_msg, self.config_undo,
        #                           self.undo_default, '(1 row)', flag='1')
        self.common.equal_sql_mdg(autovacuum_msg, self.item_autovacuum,
                        self.autovacuum_default, '(1 row)', flag='1')
        self.common.equal_sql_mdg(show_mode_msg, self.item_mode,
                                  self.default_mode, '(1 row)', flag='1')
        self.common.equal_sql_mdg(show_naptime_msg, self.item_naptime,
                                  self.default_naptime, '(1 row)', flag='1')
        self.logger.info(
            '---Opengauss_Function_Ustore_Autovacuum_Case0016 finish---')